from flask import jsonify, request
from api import api_bp
from models import HouseInfoFinal
from extensions import db
from sqlalchemy import func
import random

@api_bp.route('/house/statistics')
def house_statistics():
    """
    获取房源统计数据
    返回区域统计、户型统计和价格走势数据
    """
    try:
        # 获取区域统计（前10个区域）
        region_stats = db.session.query(
            HouseInfoFinal.region,
            func.count(HouseInfoFinal.id).label('count')
        ).group_by(HouseInfoFinal.region).order_by(func.count(HouseInfoFinal.id).desc()).limit(10).all()
        
        # 获取户型统计（前10个户型）
        house_type_stats = db.session.query(
            HouseInfoFinal.rooms,
            func.count(HouseInfoFinal.id).label('count')
        ).group_by(HouseInfoFinal.rooms).order_by(func.count(HouseInfoFinal.id).desc()).limit(10).all()
        
        # 获取价格走势（按区域分组的平均价格）
        price_trend = db.session.query(
            HouseInfoFinal.region,
            func.avg(HouseInfoFinal.price).label('avg_price')
        ).group_by(HouseInfoFinal.region).order_by(func.avg(HouseInfoFinal.price).desc()).limit(10).all()
        
        # 构造返回数据
        data = {
            'region_stats': [{'name': stat.region, 'value': stat.count} for stat in region_stats],
            'house_type_stats': [{'name': stat.rooms, 'value': stat.count} for stat in house_type_stats],
            'price_trend': [{'name': stat.region, 'value': float(stat.avg_price) if stat.avg_price else 0} for stat in price_trend]
        }
        
        return jsonify({'success': True, 'data': data})
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取统计数据失败：{str(e)}'}), 500

# 新增API接口：获取小区房源数量TOP20
@api_bp.route('/community/stats')
def community_stats():
    try:
        # 查询小区房源数量统计（TOP20）
        stats = db.session.query(
            HouseInfoFinal.address,
            func.count(HouseInfoFinal.id).label('count')
        ).group_by(HouseInfoFinal.address).order_by(func.count(HouseInfoFinal.id).desc()).limit(20).all()
        
        # 构造返回数据
        data = [{'name': stat.address, 'value': stat.count} for stat in stats]
        
        return jsonify({'success': True, 'data': data})
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取小区统计失败：{str(e)}'}), 500

# 新增API接口：获取户型占比统计
@api_bp.route('/house_type/proportion')
def house_type_proportion():
    try:
        # 查询户型占比统计
        stats = db.session.query(
            HouseInfoFinal.rooms,
            func.count(HouseInfoFinal.id).label('count')
        ).group_by(HouseInfoFinal.rooms).all()
        
        # 计算总数
        total_count = sum([stat.count for stat in stats])
        
        # 构造返回数据（百分比）
        data = []
        for stat in stats:
            percentage = round((stat.count / total_count) * 100, 2) if total_count > 0 else 0
            data.append({
                'name': stat.rooms,
                'value': percentage
            })
        
        return jsonify({'success': True, 'data': data})
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取户型占比失败：{str(e)}'}), 500

# 新增API接口：获取户型价格走势统计
@api_bp.route('/house_type/price_trend')
def house_type_price_trend():
    try:
        # 查询不同户型的平均价格
        stats = db.session.query(
            HouseInfoFinal.rooms,
            func.avg(HouseInfoFinal.price).label('avg_price')
        ).group_by(HouseInfoFinal.rooms).all()
        
        # 构造返回数据
        data = [{'name': stat.rooms, 'value': float(stat.avg_price) if stat.avg_price else 0} for stat in stats]
        
        return jsonify({'success': True, 'data': data})
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取户型价格走势失败：{str(e)}'}), 500

# 新增API接口：获取区域价格走势统计
@api_bp.route('/region/price_trend')
def region_price_trend():
    try:
        # 查询不同区域的平均价格
        stats = db.session.query(
            HouseInfoFinal.region,
            func.avg(HouseInfoFinal.price).label('avg_price')
        ).group_by(HouseInfoFinal.region).all()
        
        # 构造返回数据
        data = [{'name': stat.region, 'value': float(stat.avg_price) if stat.avg_price else 0} for stat in stats]
        
        return jsonify({'success': True, 'data': data})
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取区域价格走势失败：{str(e)}'}), 500


@api_bp.route('/search/keyword/', methods=['POST'])
def search_keyword():
    """提供首页搜索栏的联想建议。"""
    payload = request.form
    if not payload and request.is_json:
        payload = request.get_json(silent=True) or {}
    keyword = (payload.get('kw') or '').strip()
    mode = payload.get('info') or ''

    if not keyword:
        return jsonify({'code': 0, 'info': []})

    like_pattern = f'%{keyword}%'
    suggestions = []

    def collect(column, type_label, limit=5):
        rows = (
            db.session.query(
                column.label('name'),
                func.count(HouseInfoFinal.id).label('num')
            )
            .filter(
                column.isnot(None),
                column != '',
                column.ilike(like_pattern)
            )
            .group_by(column)
            .order_by(func.count(HouseInfoFinal.id).desc())
            .limit(limit)
            .all()
        )
        for row in rows:
            if not row.name:
                continue
            suggestions.append({
                't_name': row.name,
                'num': row.num,
                'type': type_label,
            })

    if '地区' in mode:
        collect(HouseInfoFinal.region, 'region', limit=4)
        collect(HouseInfoFinal.block, 'block', limit=4)
        collect(HouseInfoFinal.address, 'address', limit=4)
    else:
        collect(HouseInfoFinal.rooms, 'rooms', limit=8)

    if not suggestions:
        return jsonify({'code': 0, 'info': []})
    return jsonify({'code': 1, 'info': suggestions})
